﻿Imports System.Data.OleDb
Public Class SceneDetails
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack Then Return

        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("TwilightConn").ConnectionString)
        Dim sql As String = "INSERT INTO ScenesViewedByUsers(ScenesFKID,TwiWriter) VALUES (@f1,@f2)"
        Dim cmd = New OleDbCommand(sql, conn)
        conn.Open()
        cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f2", User.Identity.Name)
        cmd.ExecuteNonQuery()
        conn.Close()
    End Sub

    Protected Sub btn_sceneComment_Click(sender As Object, e As EventArgs) Handles btn_sceneComment.Click
        Dim avatarName As String = ""
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("TwilightConn").ConnectionString)
        Dim sql2 = "Select Avatar FROM Profile WHERE TwiWriter=@f1"
        Dim cmd2 As OleDbCommand = New OleDbCommand(sql2, conn)
        cmd2.CommandType = CommandType.Text
        cmd2.Parameters.AddWithValue("@f1", User.Identity.Name)
        conn.Open()
        Dim dr As OleDbDataReader = cmd2.ExecuteReader()
        If dr.HasRows Then
            dr.Read()
            If Not IsDBNull(dr("avatar")) Then avatarName = dr("avatar")
        End If

        Dim sql = "INSERT INTO SceneComments (TwiWriter,Comment,SceneFKID,AvatarFK) VALUES (@f1,@f2,@f3,@f4)"
        Dim cmd = New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f2", tb_sceneComment.Text)
        cmd.Parameters.AddWithValue("@f3", Request.Params("ID"))
        cmd.Parameters.AddWithValue("@f4", avatarName)
        cmd.ExecuteNonQuery()
        conn.Close()
        Response.Redirect("SceneDetails.aspx?ID=" & Request.Params("ID"))
    End Sub

    Protected Sub btn_addExistingTag_Click(sender As Object, e As EventArgs) Handles btn_addExistingTag.Click
        Dim sceneID = Request.Params("ID")
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("TwilightConn").ConnectionString)
        conn.Open()

        Dim sql = "INSERT INTO SceneTagJunction (TagFKID,SceneFKID) VALUES (@f1,@f2)"
        Dim cmd As New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@f1", ddl_tags.SelectedValue)
        cmd.Parameters.AddWithValue("@f2", sceneID)
        cmd.ExecuteNonQuery()
        conn.Close()
    End Sub

    Protected Sub btn_addNewTag_Click(sender As Object, e As EventArgs) Handles btn_addNewTag.Click
        Dim sceneID = Request.Params("ID")
        Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("TwilightConn").ConnectionString)
        Dim SqlString As String = "INSERT INTO SceneTagJunction(SceneFKID,TagFKID) VALUES (@f1,@f2)"
        conn.Open()

        If tb_newTag.Text = "" Then Return
        'split function converts the text string into an array of text tags
        Dim newTags = tb_newTag.Text.Split(",")
        'newTags is the array
        For Each newTag In newTags
            Dim newTagSql As String = "INSERT INTO Tags(Tag) VALUES(@f1)"
            Dim cmd As OleDbCommand = New OleDbCommand(newTagSql, conn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", newTag)
            cmd.ExecuteNonQuery()

            'find the id of the new (latest added) tag
            Dim getLastIDcmd As OleDbCommand = New OleDbCommand("SELECT TOP 1 ID FROM Tags ORDER BY ID DESC", conn)
            Dim dr = getLastIDcmd.ExecuteReader()
            dr.Read()
            Dim lastID = dr.GetValue(0)

            'associate the new tag with the scene
            Dim makeAssociationCmd As OleDbCommand = New OleDbCommand(SqlString, conn)
            makeAssociationCmd.CommandType = CommandType.Text
            makeAssociationCmd.Parameters.AddWithValue("@f1", sceneID)
            makeAssociationCmd.Parameters.AddWithValue("@f2", lastID)
            makeAssociationCmd.ExecuteNonQuery()
        Next
        conn.Close()
    End Sub
End Class